package com.zgjkhis.modules.system.service.mapper;

import com.zgjkhis.common.mapper.CoreMapper;
import com.zgjkhis.modules.charges.domain.vo.ExpenseDetail;
import com.zgjkhis.modules.charges.service.dto.InfoDrugCostDto;
import com.zgjkhis.modules.his.service.dto.DataPrescription;
import com.zgjkhis.modules.his.service.dto.DataPrescriptionDetail;
import com.zgjkhis.modules.statistics.resp.DrugRefundResp;
import com.zgjkhis.modules.system.domain.InfoDrug;
import com.zgjkhis.modules.system.domain.vo.DrugSaleVo;
import com.zgjkhis.modules.system.req.RefundReq;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

/**
 * @Author yzq
 * @Description 处方详情
 * @Date 2021/6/23 9:48
 * @Param
 * @return
 **/
@Repository
public interface InfoDrugMapper extends CoreMapper<InfoDrug> {



    @Select("<script>SELECT i.prescription_type as name,sum(t.discount_price*t.quantity) as discountMoney,sum(t.price*t.quantity) as money,sum(t.discount_price*t.refund_count) as refund FROM his_prescription_info_drug  t \n" +
            "left join his_prescription_info i on t.info_no=i.info_no\n" +
            "where t.order_no=#{orderNo}\n" +
            "group by prescription_type</script>")
    List<ExpenseDetail> queryExpense(@Param("orderNo") String orderNo);

    @Select("<script>select t.info_no as id,t.patient_name as name,REPLACE(o.patient_age, '岁', '') as age,IF(o.patient_sex=1,'男','女') as sex,t.doc_name as doctor_name,\n" +
            "o.pay_time as prescribe_time ,t.num as quantity_day, t.num*2 as quantity,t.total_pay as price_total,'PAYED' as payment_status,'HIS' as data_source,\n" +
            "'NEW' as process_status,'遵医嘱' as description,'接口程序' as creator_name,(t.total_pay/t.num) as price,'1' as onepres_packages,IFNULL(o.department_name,'中医男科985') as department_name \n" +
            "from (select * from his_prescription_info where create_time >='2021-12-06' and prescription_type=3 and info_no not in (select info_no from his_info_transmission)) t \n" +
            "left join his_prescription_order o  on o.order_no=t.order_no </script>")
    List<DataPrescription> queryDataList();

    @Select("<script>select t.info_no as id,t.drug_id as granule_id,t.drug_name as granule_name,(t.quantity/#{num}) as dose_herb,'0' as dose,\n" +
            "'0' as equivalent,t.quantity*t.price as price\n" +
            "from his_prescription_info_drug t\n" +
            "where t.info_no=#{infoNo}</script>")
    List<DataPrescriptionDetail> queryDataDetailList(@Param("infoNo") String id, @Param("num") BigDecimal quantityDay);

    @Select("<script>select t.*,d.drug_type from (\n" +
            "select * from his_prescription_info_drug  where order_no=#{orderNo}\n" +
            ")t left join his_drug d on t.drug_id=d.id\n" +
            "order by t.info_no asc</script>")
    List<InfoDrug> queryInfoDrugList(@Param("orderNo")String orderNo);

    /**
     * @Author yzq
     * @Description 根据订单号，分组查询处方总金额
     * @Date 2021/9/9 14:45
     * @Param [orderNo]
     * @return java.util.List<com.zgjkhis.modules.charges.service.dto.InfoDrugCostDto>
     **/
    @Select("<script>select i.id as infoId,IFNULL(sum(t.discount_price*t.quantity),0) as totalFee from (\n" +
            " select * from his_prescription_info_drug \n" +
            " where order_no=#{orderNo} and delete_flag = 0 and is_cancel=0\n" +
            " )t\n" +
            " left join his_prescription_info i on t.info_no=i.info_no\n" +
            " group by i.id</script>")
    List<InfoDrugCostDto> queryTotalFeeForInfo(@Param("orderNo") String orderNo);

    /**
     * 根据处方单号，查询已支付过的药品清单个数
     * @param infoNo
     * @return
     */
    @Select("<script>select count(1) from his_prescription_info_drug t where t.info_no=#{infoNo} and t.delete_flag=0 and t.status!=0</script>")
    int queryInfoDrugByInfoNo(@Param("infoNo") String infoNo);

    /**
     * 根据处方单号查询处方列表
     * @param infoNo
     * @return
     */
    @Select("<script>select t.*,d.drug_type from (\n" +
            "select * from his_prescription_info_drug  where info_no=#{infoNo}\n" +
//            "<if test=\"drug.drugType !=null and drug.drugType != ''\">\n" +
//            " and delete_flag = #{type}\n" +
//            "</if>\n" +
            ")t left join his_drug d on t.drug_id=d.id\n" +
            "order by t.info_no asc</script>")
    List<InfoDrug> queryInfoDrugListByInfoNo(@Param("infoNo") String infoNo);

    @Select("<script>SELECT\n" +
            "  drug_name,\n" +
            "  pharmacyName,\n" +
            "  SUM(quantity -refund_count)        AS quantity,\n" +
            "  SUM((quantity-refund_count)*price) AS sum_price\n" +
            "FROM\n" +
            "  his_prescription_info_drug a,\n" +
            "  his_prescription_order o,\n" +
            "  his_pharmacy d\n" +
            "WHERE\n" +
            "  a.order_no=o.order_no\n" +
            " and o.pharmacy_id=#{pharmacyId}\n" +
            " and o.adjust_time BETWEEN #{startDate} and #{endDate}\n" +
            "  and o.pharmacy_id=d.id\n" +
            "GROUP BY\n" +
            "  drug_id\n" +
            "ORDER BY\n" +
            "  sum_price DESC</script>")
    List<DrugSaleVo> drugSale(@Param("pharmacyId") String pharmacyId, @Param("startDate") String startDate, @Param("endDate") String endDate);


    @Select("<script>SELECT\n" +
            "\t* \n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "d.ware_housing_no as order_no,\n" +
            "            10 AS delivery_type,\n" +
            "            ao.drug_name,\n" +
            "            c.create_time as adjust_time,\n" +
            "            ao.patient_name,\n" +
            "            ao.doc_name,\n" +
            "            d.batch_no,\n" +
            "(\n" +
            "            case\n" +
            "               when (sta=2 or sta=3) and c.dive_num = ao.quantity then c.dive_num-ao.refund_count+ao.operate_num \n" +
            "               when (sta=2 or sta=3) and c.dive_num <![CDATA[< ]]> ao.quantity then c.dive_num-(ao.refund_count-(ao.quantity-c.dive_num))+ao.operate_num \n" +
            "               else c.dive_num \n" +
            "             end \n" +
            ") as quantity,\n" +
            "(\n" +
            "             case\n" +
            "                when (sta=2 or sta=3) and c.dive_num = ao.quantity then c.plus_num-(c.dive_num-ao.refund_count)-ao.operate_num \n" +
            "                when (sta=2 or sta=3) and c.dive_num <![CDATA[< ]]> ao.quantity then c.plus_num-(c.dive_num-(ao.refund_count-(ao.quantity-c.dive_num)))-ao.operate_num \n" +
            "                else 0\n" +
            "             end\n" +
            ") as bs"+
            "\tFROM\n" +
            "(\n" +
            "SELECT\n" +
            "\ta.info_no,\n" +
            "\ta.id AS aid,\n" +
            "\to.info_no AS oinfo_no,\n" +
            "\ta.drug_id,\n" +
            "\ta.pharmacy_id,\n" +
            "\to.adjust_time, \n" +
            "\ta.drug_name, \n" +
            "\to.patient_name, \n" +
            "\to.doc_name, \n" +
            "\ta.status as sta, \n" +
            "\ta.refund_count, \n" +
            "\ta.quantity, \n" +
            "\ta.operate_num \n" +
            "FROM\n" +
            "\this_prescription_info_drug AS a\n" +
            "\tLEFT JOIN his_prescription_info AS o ON a.info_no = o.info_no \n" +
            "WHERE\n" +
            "\ta.drug_id = #{drugId} AND a.pharmacy_id = #{pharmacyId}\n" +
            "\t<if test=\"startDate !=null  and startDate!='' \">\n" +
            "\tAND o.adjust_time BETWEEN #{startDate} AND #{endDate}\n" +
            "\t</if> \n" +
            "\t) as ao\n" +
            "\t,\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "create_time,\n" +
            "dive_num,\n" +
            "plus_num,\n" +
            "warehousing_id,\n" +
            "info_drug_id \n" +
            "\tFROM\n" +
            "his_prescription_drug_comfire \n" +
            "\tWHERE\n" +
            "1 = 1 \n" +
            "<if test=\"startDate !=null  and startDate!='' \">\n" +
            "\tAND create_time BETWEEN #{startDate} AND #{endDate} \n" +
            "</if>\n" +
            ") c ,\n" +
            "(\n" +
            "SELECT\n" +
            "\tbatch_no,\n" +
            "\tware_housing_no,\n" +
            "\tid \n" +
            "FROM\n" +
            "\this_drug_warehousing_detail \n" +
            "WHERE\n" +
            "\tdrug_id = #{drugId} ) d\n" +
            "\t\n" +
            "WHERE\n" +
            "\tc.info_drug_id = ao.aid \n" +
            "\tAND d.id = c.warehousing_id \n" +
            "\tAND c.create_time IS NOT NULL UNION\n" +
            "SELECT\n" +
            "\te.ware_housing_no AS order_no,\n" +
            "\ta.delivery_type AS delivery_type,\n" +
            "\tb.drug_name,\n" +
            "\tcheck_time AS adjust_time,\n" +
            "\ta.collect_use_name AS patient_name,\n" +
            "\ta.create_user_name AS doc_name,\n" +
            "\te.batch_no,\n" +
            "\tdelivery_num AS quantity, \n" +
            "\t0 AS bs \n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "* \n" +
            "\tFROM\n" +
            "his_drug_delivery \n" +
            "\tWHERE\n" +
            "pharmacy_id = #{pharmacyId} AND STATUS = 2 \n" +
            "<if test=\"startDate !=null  and startDate!='' \">\n" +
            "AND check_time BETWEEN #{startDate} AND #{endDate} \n" +
            "</if>\n" +
            ") a,\n" +
            "(\n" +
            "SELECT\n" +
            "\t* \n" +
            "FROM\n" +
            "\this_drug_delivery_detail \n" +
            "WHERE\n" +
            "\tdrug_id = #{drugId} ) b,\n" +
            "( SELECT * FROM his_drug_warehousing_detail WHERE drug_id = #{drugId} ) e\n" +
            "\tWHERE a.delivery_no = b.delivery_no AND b.warehousing_detail_id = e.id ) AS z " +
            "            WHERE\n" +
            "            (quantity > 0 or bs>0) \n" +
            "            <if test=\"userName !=null and userName!=''\">\n" +
            "            and patient_name LIKE CONCAT('%',#{userName},'%')\n" +
            "            </if>\n" +
            "            <if test=\"docName !=null and docName!=''\">\n" +
            "            and doc_name LIKE CONCAT('%',#{docName},'%')\n" +
            "            </if>\n" +
            "            <if test=\"type !=null and type!=''\">\n" +
            "               and delivery_type = #{type}\n" +
            "            </if>\n" +
            "   </script>")
    List<DrugSaleVo> drugStock(@Param("pharmacyId")String pharmacyId,@Param("drugId")String drugId,@Param("startDate")String startDate,@Param("endDate")String endDate,@Param("userName")String userName,@Param("docName")String docName,@Param("type")String type);

    @Select("<script>select t.info_no as id,t.patient_name as name,REPLACE(o.patient_age, '岁', '') as age,IF(o.patient_sex=1,'男','女') as sex,t.doc_name as doctor_name,\n" +
            "t.create_time as prescribe_time ,t.num as quantity_day, t.num*2 as quantity,t.total_pay as price_total,'PAYED' as payment_status,'HIS' as data_source,\n" +
            "'NEW' as process_status,'遵医嘱' as description,'接口程序' as creator_name,(t.total_pay/t.num) as price,'1' as onepres_packages,IFNULL(o.department_name,'中医男科985') as department_name \n" +
            "from (select * from his_prescription_info where create_time >='2021-12-06' and prescription_type=3 and order_no=#{orderNo} and  info_no not in (select info_no from his_info_transmission)) t \n" +
            "left join his_prescription_order o  on o.order_no=t.order_no</script>")
    List<DataPrescription> queryInfoByOrderNo(@Param("orderNo") String orderNo);


    @Select("<script>  \n" +

            "select b.*,max(c.create_time) as refundTime from (\n" +
            " select d.drug_name,d.drug_id,sum(d.refund_count) as refundCount,sum(d.refund_count*d.discount_price)  as refundTotal,max(d.create_time) as createTime,max(order_no) as orderNo"+
            " from (" +
            "select * from his_prescription_info_drug where order_no in(\n" +
            "\tselect order_no from his_prescription_refund where 1=1 \n" +
            "<if test=\"req.refundStartTime != null and req.refundEndTime != null \">\n" +
            " and create_time  &gt; #{req.refundStartTime}\n" +
            " and create_time  &lt;  str_to_date(DATE_ADD(#{req.refundEndTime},INTERVAL 1 DAY), '%Y-%m-%d %H')\n" +
            "</if>" +
            "\t) and refund_count> 0 and status in(3,4) and pharmacy_id=#{req.pharmacyId}" +
            "            <if test=\"req.drugName !=null and req.drugName !=''\">\n" +
            "            and drug_name LIKE CONCAT('%',#{req.drugName},'%')\n" +
            "            </if>\n" +
            "<if test=\"req.startTime != null and req.endTime != null \">\n" +
            " and create_time  &gt; #{req.startTime}\n" +
            " and create_time  &lt;  str_to_date(DATE_ADD(#{req.endTime},INTERVAL 1 DAY), '%Y-%m-%d %H')\n" +
            "</if>" +
            ") d \n" +
            " group by drug_id,drug_name "+
            ") b \n" +
            "left join his_prescription_refund c on b.orderNo=c.order_no\n" +
            "group by b.drug_id,b.drug_name,b.refundCount,b.refundTotal"+
            "</script>")
    List<DrugRefundResp> refundList(@Param("req") RefundReq refundReq);
}
